package com.zretc.support.dao;

import com.zretc.support.entity.Message;
import com.zretc.support.entity.Score;
import com.zretc.support.entity.ScoreToPart;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

public interface ScoreDao extends JpaRepository<Score, Integer>, JpaSpecificationExecutor<Score>,
        CrudRepository<Score, Integer>, Serializable {

    //根据学生编号、考试名称、学生名称查询
    @Query("select s from Score s where s.student.userId = ?1 or s.exam.examName like %?1% or s.student.userName like %?1%")
    Page<Score> selectByIdOrName(String keywords, Pageable pageable);

    //每个考试的平均分、最高分、最低分
    @Query("select new com.zretc.support.entity.ScoreToPart (s.exam.examId,s.exam.examName,AVG(s.totalScore),MAX(s.totalScore),MIN(s.totalScore)) " +
            "FROM Score  s where s.exam.examName like %?1% GROUP BY  s.exam.examId  ORDER BY s.exam.examId ")
    Page<ScoreToPart> getPart(String keywords, Pageable pageable);

    //获取某场考试的所有学生的成绩
    @Query("select s from Score s where s.exam.examId=?1")
    List<Score> getScoresByExamId(long examId);

    //根据学生编号查询学生成绩
    @Query("select s from Score s where s.student.userId = ?1 and s.exam.examName like %?2% order by s.exam.examBegin desc ")
    Page<Score> getScoreByUserId(String userId,String examName,Pageable pageable);

    //根据examId、userId获得score
    @Query("select s from Score s where s.exam.examId=?1 and s.student.userId=?2")
    Score getScoresByExamIdAndUerId(long examId,String userId);

    //模糊查询获取某学生的成绩
    @Query("select s from Score s where s.student.userId=?1 and s.exam.examName like %?2%")
    Page<Score> getScoresByUserId(String userId,String keywords,Pageable pageable);


}
